package com.example.baseapp.activity

import android.content.ContentValues
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.os.Bundle
import android.util.Log
import android.widget.Toast
import androidx.appcompat.app.AppCompatActivity
import com.example.baseapp.R
import kotlinx.android.synthetic.main.activity_database_transaction.*

/**
 * @作者：HuaCon
 *
 * @类描述：使用数据库事物提交操作
 *
 */
class DatabaseTransactionActivity : AppCompatActivity() {
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_database_transaction)
        init()
    }

    /**
     * 配合AndroidStudio 4.1+版本，
     * 可视化数据库Database Inspector选项卡
     * 查看数据库更方便
     */
    private fun init() {
        var myDbHelper = MyDbHelper(this, "MyDb", 1)
        //创建数据库
        bt_createDb.setOnClickListener {
            //执行writableDatabase方法或者readableDatabase方法后会执行onCreate方法，如果版本号
            //高于低版本，会触发onUpgrade方法
            //当数据库不可写入的时候（如磁盘空间已满），readableDatabase方法返回的对象将以只读的方式打开数据库
            //而writableDatabase方法则将抛出异常
            myDbHelper.writableDatabase
        }
        //升级数据库版本
        bt_upgradeDb.setOnClickListener {
            myDbHelper = MyDbHelper(this, "MyDb", 2)
            myDbHelper.writableDatabase
        }

        //添加数据
        bt_addData.setOnClickListener {
            val database = myDbHelper.writableDatabase
            for (i in 0 until 10) {
                val studentVlu = ContentValues().apply {
                    put("name", "HuaCo$i")
                    put("age", ((1..5).random() * i) - ((1..3).random()))
                    put("score", 60.6 + i)
                    put("sex", i % 2)
                }
                //表名，--，数据
                database.insert("Student", null, studentVlu)
            }

            val teacherVlu = ContentValues().apply {
                put("subject", "chinese")
                put("className", "五年级18班")
                put("name", "HuaCoWang")
            }
            database.insert("Teacher", null, teacherVlu)
            Toast.makeText(applicationContext, "添加成功", Toast.LENGTH_SHORT).show()
        }

        //修改数据
        bt_updateData.setOnClickListener {
            val database = myDbHelper.writableDatabase
            val values = ContentValues()
            values.put("age", 50)
            //表名，数据，查询条件？为占位符，占位符对应的真实内容
            database.update("Student", values, "name=?", arrayOf("HuaCo1"))
            Toast.makeText(applicationContext, "修改成功", Toast.LENGTH_SHORT).show()
        }

        //删除数据
        bt_delData.setOnClickListener {
            val database = myDbHelper.writableDatabase
            //表名，查询提交，占位符对应的真实内容
            database.delete("Student", "age<=?", arrayOf("10"))
            Toast.makeText(applicationContext, "删除成功", Toast.LENGTH_SHORT).show()
        }

        //查询数据
        bt_queryData.setOnClickListener {
            val database = myDbHelper.writableDatabase
            val cursor = database.query("Student", null, null, null, null, null, null)
            if (cursor.moveToNext()) {
                do {
                    val name = cursor.getString(cursor.getColumnIndex("name"))
                    val age = cursor.getInt(cursor.getColumnIndex("age"))
                    val score = cursor.getFloat(cursor.getColumnIndex("score"))
                    val sex = cursor.getInt(cursor.getColumnIndex("sex"))
                    Log.d(
                        "dataBase", "queryData:" +
                                """
                        name is $name,
                        age is $age,
                        score is $score
                        sex is $sex
                    """.trimIndent()
                    )
                } while (cursor.moveToNext())
                Toast.makeText(applicationContext, "查询成功，请查看log", Toast.LENGTH_SHORT).show()
            }

        }

        //数据库事务提交
        bt_daTransaction.setOnClickListener {
            //从beginTransaction到setTransactionSuccessful之间的代码是一个完整的事务，要么成功要么不变
            //最后不要忘记关闭事务endTransaction
            val database = myDbHelper.writableDatabase
            try {
                //开启事务
                database.beginTransaction()
                database.delete("Student", null, null)
                if (true) throw IllegalStateException()

                val values =
                    cvOf("name" to "HuaCo is handsome", "age" to 13, "score" to 100, "sex" to 1)
                val values2 = cvOf(
                    "name" like "HuaCo is cool",
                    "age" like 18,
                    "score" like 99,
                    "sex" like 0
                )
                database.insert("Student", null, values)
                database.insert("Student", null, values2)
                //事务成功
                database.setTransactionSuccessful()
            } catch (e: Exception) {
                Toast.makeText(applicationContext, "事务提交失败，数据无变化", Toast.LENGTH_SHORT).show()
            } finally {
                //结束事务
                database.endTransaction()
            }
        }
    }

    /**
     * 补充知识点：使用infix函数调高代码可读性
     * Pair结构体就是<Key,value>形式的
     * infix 函数有两个严格的限制：
     * 1.infix 函数不能定义成顶层函数，它必须是某个的成员函数，也可以用扩展函数的方式定义到某个类当中
     * 2.infix 函数只能接受一个参数，至于参数类型是没有显示的
     * 去看to函数的实现
     */
    private infix fun <A, B> A.like(that: B): Pair<A, B> = Pair(this, that)


    /**
     * 继承原生SQLiteOpenHelper抽象类
     * 此类用于创建数据库，表，和升级数据库使用
     */
    internal class MyDbHelper(private val pContext: Context?, dbName: String?, version: Int) :
        SQLiteOpenHelper(pContext, dbName, null, version) {

        private val createStudentTable = "create table Student(" +
                //id 为主键 自增
                "id integer primary key autoincrement," +
                //文本类型
                "name text," +
                //整型
                "age integer," +
                //浮点型
                "score real," +
                //二进制类型
                "sex blob)"

        private val createTeacherTable = "create table Teacher(" +
                "id integer primary key autoincrement," +
                "subject text," +
                "className text," +
                "name text)"


        override fun onCreate(db: SQLiteDatabase?) {
            db?.execSQL(createStudentTable)
            Toast.makeText(pContext, "数据库创建成功", Toast.LENGTH_SHORT).show()
        }

        override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
            if (oldVersion <= 1) db?.execSQL(createTeacherTable)
            Toast.makeText(pContext, "数据库版本已更新", Toast.LENGTH_SHORT).show()
        }
    }

    /**
     * 补充知识点： 简化ContentValues的用法
     * 1.vararg是kotlin的可变长参数类型 -> java <T...>
     * 2.Any是所有类的基类 ->java 的Object ，Any?表示可以传入空值
     * 3.Pair是一种键值对的数据结构
     * 4.智能推断，进入条件语句后，因为前方已经做过类型判断，后方无需往下转型
     * 利用kotlin的语法糖配合apply函数，让代码更优雅
     */
    fun cvOf(vararg pairs: Pair<String, Any?>) = ContentValues().apply {
        pairs.forEach {
            val key = it.first
            when (val value = it.second) {
                is Int -> put(key, value)
                is Short -> put(key, value)
                is Long -> put(key, value)
                is Float -> put(key, value)
                is String -> put(key, value)
                is Byte -> put(key, value)
                null -> putNull(key)
            }
        }
    }

}




